﻿namespace PsychSurvey
{
    using System;
    using System.Linq;
    using System.Text;
    using System.Collections.Generic;
    using PsychSurvey.ResearchId;
    using System.Web.SessionState;
    using System.Runtime.Serialization;
    using OfficeOpenXml;
    using System.IO;
    using System.Web;
    using PsychSurvey.Properties;

    public partial class dbDataContext
    {
#if DEBUG
        public dbDataContext() : this(Settings.Default.ConnectionString_local) { }
#else
        public dbDataContext() : this(Settings.Default.ConnectionString) { } 
#endif

    }
    public enum ClaimEnum { Gender = 1, Birthdate = 2, Address = 3, City = 4, State = 5, ZipCode = 6, Country = 7, GivenName = 8, Surname = 10, EmailAddress = 11 };
    [DataContractAttribute]
    public class StudyConsolidatedOrderedResults
    {
        [DataMember]
        public Guid StudyId { get; set; }
        [DataMember]
        public int ConditionId { get; set; }
        [DataMember]
        public string ConditionName { get; set; }
        [DataMember]
        public DateTime? DateOpen { get; set; }
        [DataMember]
        public DateTime? DateClose { get; set; }
        [DataMember]
        public DateTime? OpenDate { get; set; }
        [DataMember]
        public int? DayStartOffset { get; set; }
        [DataMember]
        public int StudyWaveId { get; set; }
        [DataMember]
        public string StudyWaveName { get; set; }
        [DataMember]
        public Guid SurveyId { get; set; }
        [DataMember]
        public int SurveyPageId { get; set; }
        [DataMember]
        public string PageContentOriginal { get; set; }
        [DataMember]
        public string PageContentModified { get; set; }
        [DataMember]
        public string PageName { get; set; }
        [DataMember]
        public int? StudyWaveDisplayOrder { get; set; }
        [DataMember]
        public int SurveyDisplayOrder { get; set; }
        [DataMember]
        public int SurveyPageDisplayOrder { get; set; }
        [DataMember]
        public string SurveyName { get; set; }
        [DataMember]
        public int? IndividualId { get; set; }
    }
    [DataContractAttribute]
    public class ConditionCount
    {
        [DataMember]
        public int ConditionId { get; set; }
        [DataMember]
        public string ConditionName { get; set; }
        [DataMember]
        public int Participants { get; set; }
    }
    public partial class SurveyPage
    {
        private int _displayOrder = 0;
        public int DisplayOrder
        {
            get
            {
                return _displayOrder;
            }
            set
            {
                _displayOrder = value;
            }
        }
        public static SurveyPage GetSurveyPage(int SurveyPageId)
        {
            dbDataContext db = new dbDataContext();
            return db.SurveyPages.SingleOrDefault(x => x.SurveyPageId == SurveyPageId);
        }
        public static List<SurveyPage> GetSurveyPage(Guid[] SurveyIds)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.SurveyPages
                    where x.SurveySurveyPages.Any(y => SurveyIds.Contains(y.SurveyId))
                    select x).ToList();
        }
    }
    public partial class Individual
    {
        public static Individual GetIndividual(int UserId)
        {
            dbDataContext db = new dbDataContext();
            Individual i = (from y in db.Individuals
                            where y.IndividualId == UserId
                            select y).FirstOrDefault();
            return i;
        }
        public static Individual CreateIndividualIfNotExists(string Username, int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            Individual i = (from y in db.Individuals
                            where y.PPID == IndividualId.ToString()
                            select y).FirstOrDefault();
            if (i == null)
            {
                return CreateIndividual(IndividualId, Username, db);
            }
            else
            {
                return i;
            }
        }
        public static int CountParticipants(Guid StudyId)
        {
            dbDataContext db = new dbDataContext();
            var Results = from x in db.IndividualConditions
                          where x.IsTesting == false
                          select new { x.IndividualId, x.StudyId };
            int Count = Results.Count();
            return Count;
        }
        public static Individual CreateIndividual(int IndividualId, string UserName, dbDataContext db)
        {
            // Create a new Individual
            UserAuthenticate auth = new UserAuthenticate(true);
            Individual i = new Individual();
            i.PPID = IndividualId.ToString();
            db.Individuals.InsertOnSubmit(i);
            db.SubmitChanges();
            return i;
        }
        public static void RetrieveClaimsToSession(int IndividualId, string UserName, Guid StudyId, HttpSessionState Session)
        {
            //Retrieve the claims from this particular study
            List<Claim> Claims = PsychSurvey.Study.GetClaimsAdded(StudyId);
            List<int> ClaimIds = (from x in Claims select x.ClaimId).ToList();
            UserAuthenticate a = new UserAuthenticate(true);
            List<IndividualClaimsView> ClaimsValues = a.GetClaims(UserName, ClaimIds.ToArray()).ToList();
            Session.Add("Claims", ClaimsValues);
        }
    }
    public class ConditionReport
    {
        public int ConditionId { get; set; }
        public int TotalParticipants { get; set; }
    }
    public partial class Claim
    {
        public static List<Claim> GetClaims()
        {
            dbDataContext db = new dbDataContext();
            return (from y in db.Claims
                    orderby y.ClaimName
                    select y).ToList();
        }
    }
    public partial class Study
    {
        public static string GetStudyUrl(Guid StudyId)
        {
            
            {
#if (DEBUG)
                Uri StudyUri = new Uri(new Uri(Settings.Default.ApplicationRoot_local),
                  Settings.Default.StudyPageUrl_local + "?" +
                      Settings.Default.StudyPageQueryStringName + "=" +
                    StudyId.ToString());
#else
                 Uri StudyUri = new Uri(new Uri(Settings.Default.ApplicationRoot),
                    Settings.Default.StudyPageUrl + "?" +
                        Settings.Default.StudyPageQueryStringName + "=" +
                    StudyId.ToString());
#endif
                return StudyUri.AbsoluteUri;
            }
        }
        public static List<Study> GetStudyList()
        {
            dbDataContext db = new dbDataContext();
            var Results = db.Studies.Select(x => x);
            return Results.ToList();
        }
        public static List<StudyClaim> GetStudyClaims(Guid[] StudyIds)
        {
            dbDataContext db = new dbDataContext();
            return (from y in db.StudyClaims
                    where StudyIds.Contains(y.StudyId)
                    select y).ToList();
            db.Dispose();
        }
        public string CreateExcelDocument(HttpServerUtility Server, string ApplicationRoot)
        {
            string FileName = Server.MapPath(ApplicationRoot + "/ExcelDocTemp/") + Guid.NewGuid().ToString().Replace(" ", "") + ".xlsx";
            FileInfo newFile = new FileInfo(FileName);
            using (ExcelPackage xlPackage = new ExcelPackage(newFile))
            {
                dbDataContext db = new dbDataContext();
                xlPackage.Workbook.Properties.Title = this.StudyName + "-" + System.DateTime.Now.ToString();
                xlPackage.Workbook.Properties.Author = "PsychSurvey";
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(this.StudyName);
                Dictionary<string, int> ColumnLookup = new Dictionary<string, int>();
                //Create the columnnames
                List<string> ColumnNames = (from x in db.SurveyResults
                                            where x.SurveyPageCompleted.StudyId == this.StudyId
                                            select x.ControlName).Distinct().ToList();
                worksheet.Cell(1, 1).Value = "Participant#";
                int Column = 2;
                worksheet.Cell(1, Column).Value = "StudyId";
                ColumnLookup.Add("StudyId", Column); Column++;
                worksheet.Cell(1, Column).Value = "StudyName";
                ColumnLookup.Add("StudyName", Column); Column++;
                worksheet.Cell(1, Column).Value = "ConditionId";
                ColumnLookup.Add("ConditionId", Column); Column++;
                worksheet.Cell(1, Column).Value = "ConditionName";
                ColumnLookup.Add("ConditionName", Column); Column++;
                // Grab every kind of claim from the database to create column headers
                List<Claim> Claims = (from z in db.Claims
                                      select z).ToList();
                foreach (Claim myClaim in Claims)
                {
                    worksheet.Cell(1, Column).Value = myClaim.ClaimName;
                    ColumnLookup.Add(myClaim.ClaimName, Column); Column++;
                }
                // Grab every unique variable name in the database to create column headers
                foreach (string ColumnName in ColumnNames)
                {
                    worksheet.Cell(1, Column).Value = ColumnName;
                    ColumnLookup.Add(ColumnName, Column);
                    Column++;
                }
                // Grab each individual in the condition (INDIVIDUALCONDITION Table)
                int Row = 2;
                List<IndividualCondition> Individuals = (from y in db.IndividualConditions
                                                         where y.StudyId == this.StudyId
                                                         select y).ToList();
                foreach (IndividualCondition ic in Individuals)
                {
                    //Grab the individual's claims and add them to the sheet
                    foreach (IndividualClaim myIndividualClaim in ic.Individual.IndividualClaims)
                    {
                        worksheet.Cell(Row, ColumnLookup[myIndividualClaim.Claim.ClaimName]).Value = myIndividualClaim.Value;
                    }
                    // Add the set values
                    worksheet.Cell(Row, 1).Value = ic.IndividualId.ToString();
                    worksheet.Cell(Row, ColumnLookup["StudyId"]).Value = ic.StudyId.ToString();
                    worksheet.Cell(Row, ColumnLookup["StudyName"]).Value = ic.Study.StudyName;
                    worksheet.Cell(Row, ColumnLookup["ConditionId"]).Value = ic.ConditionId.ToString();
                    worksheet.Cell(Row, ColumnLookup["ConditionName"]).Value = ic.Condition.ConditionName;
                    // Grab the Individual's specific results
                    List<SurveyResult> IndividualResults = (from x in db.SurveyResults
                                                            where x.SurveyPageCompleted.StudyId == this.StudyId
                                                            && x.SurveyPageCompleted.ConditionId == ic.ConditionId
                                                            && x.SurveyPageCompleted.IndividualId == ic.IndividualId
                                                            orderby x.SurveyPageCompleted.StudyWaveId,
                                                            x.SurveyPageCompleted.SurveyId,
                                                            x.SurveyPageCompleted.SurveyPageId,
                                                            x.ControlName
                                                            select x).ToList();
                    foreach (SurveyResult sr in IndividualResults)
                    {
                        // Lookup each column and save the value
                        worksheet.Cell(Row, ColumnLookup[sr.ControlName]).Value = sr.ControlValue;
                    }
                    Row++;
                }

                xlPackage.Save();
                return FileName;
            }
        }
        public bool RemoveExcelDocument(string FileName)
        {
            FileInfo newFile = new FileInfo(FileName);
            newFile.Delete();
            return true;
        }
        public static Study GetStudy(Guid StudyId)
        {
            dbDataContext db = new dbDataContext();
            return db.Studies.SingleOrDefault(x => x.StudyId == StudyId);
        }
        public static List<Claim> GetClaimsNotAdded(Guid StudyId)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.Claims
                    where !(x.StudyClaims.Any(y => y.StudyId == StudyId))
                    orderby x.ClaimName
                    select x).ToList();
        }
        public static void UpdateClaims(Guid StudyId, List<Claim> ClaimsAdded)
        {
            dbDataContext db = new dbDataContext();
            List<StudyClaim> ExistingClaims = (from x in db.StudyClaims
                                               where x.StudyId == StudyId
                                               select x).ToList();
            db.StudyClaims.DeleteAllOnSubmit(ExistingClaims);
            db.SubmitChanges();
            foreach (Claim newClaim in ClaimsAdded)
            {
                StudyClaim newSc = new StudyClaim();
                newSc.StudyId = StudyId;
                newSc.ClaimId = newClaim.ClaimId;
                db.StudyClaims.InsertOnSubmit(newSc);
            }
            db.SubmitChanges();
        }
        public static List<Claim> GetClaimsAdded(Guid StudyId)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.StudyClaims
                    where x.StudyId == StudyId
                    select x.Claim).ToList();
        }

        public static List<StudyClaim> GetStudyClaimAdded(Guid StudyId, out dbDataContext db)
        {
            db = new dbDataContext();
            return (from x in db.StudyClaims
                    where x.StudyId == StudyId
                    select x).ToList();
        }
        public IndividualCondition GetConditionForIndividual(int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            IndividualCondition myIndividualCondition = db.IndividualConditions.SingleOrDefault(x => x.IndividualId == IndividualId & x.StudyId == this.StudyId);
            return myIndividualCondition;
        }
        public static IndividualCondition GetConditionForIndividual(Guid StudyId, int IndividualId)
        {

            dbDataContext db = new dbDataContext();
            IndividualCondition myIndividualCondition = db.IndividualConditions.SingleOrDefault(x => x.IndividualId == IndividualId & x.StudyId == StudyId);
            return myIndividualCondition;
        }
        public StudyWave GetNextStudyWave(int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            IndividualCondition IndividualCondition = GetConditionForIndividual(IndividualId);
            return (from myWave in db.StudyWaves
                    where IndividualCondition.DateStart.AddDays(myWave.DayStartOffset.Value) < DateTime.Now
                    && DateTime.Now < IndividualCondition.DateStart.AddDays(myWave.DayEndOffset.Value)
                    && myWave.StudyId == this.StudyId
                    orderby myWave.DayStartOffset
                    select myWave).FirstOrDefault();
        }
        public void GetFutureStudyWaveAppointment(int IndividualId, out DateTime? StartDate, out DateTime? EndDate)
        {
            dbDataContext db = new dbDataContext();
            IndividualCondition IndividualCondition = GetConditionForIndividual(IndividualId);
            StudyWave FutureWave = (from myWave in db.StudyWaves
                                    where IndividualCondition.DateStart.AddDays(myWave.DayStartOffset.Value) > DateTime.Now
                                    && DateTime.Now < IndividualCondition.DateStart.AddDays(myWave.DayEndOffset.Value)
                                    && myWave.StudyId == this.StudyId
                                    orderby myWave.DayStartOffset
                                    select myWave).FirstOrDefault();
            if (FutureWave == null)
            {
                StartDate = null;
                EndDate = null;
            }
            else
            {
                StartDate = IndividualCondition.DateStart.AddDays(FutureWave.DayStartOffset.Value);
                EndDate = IndividualCondition.DateStart.AddDays(FutureWave.DayEndOffset.Value);
            }
            return;
        }
        public static void ReportSurveyPageResults(int IndividualId, int SurveyPageId, Guid SurveyId, int StudyWaveId
            , int ConditionId, Guid StudyId, string PageResults
            , string[] Names, string[] Values
            )
        {
            dbDataContext db = new dbDataContext();
            // Get the study wave to help with variable names
            StudyWave sw = (from y in db.StudyWaves
                            where y.StudyWaveId == StudyWaveId
                            select y).FirstOrDefault();
            // Delete the old records
            SurveyPageCompleted old = (from x in db.SurveyPageCompleteds
                                       where x.StudyId == StudyId
                                       & x.StudyWaveId == StudyWaveId
                                       & x.SurveyId == SurveyId
                                       & x.ConditionId == ConditionId
                                       & x.SurveyPageId == SurveyPageId
                                       && x.IndividualId == IndividualId
                                       select x).SingleOrDefault();
            if (old != null)
            {
                foreach (SurveyResult oldsr in old.SurveyResults)
                {
                    db.SurveyResults.DeleteOnSubmit(oldsr);
                }
                db.SurveyPageCompleteds.DeleteOnSubmit(old);
                db.SubmitChanges();
            }
            SurveyPageCompleted newSurveyPageCompleted = new SurveyPageCompleted();
            newSurveyPageCompleted.DateCompleted = DateTime.Now;
            newSurveyPageCompleted.IndividualId = IndividualId;
            newSurveyPageCompleted.PageContent = PageResults;
            newSurveyPageCompleted.SurveyPageId = SurveyPageId;
            newSurveyPageCompleted.SurveyId = SurveyId;
            newSurveyPageCompleted.StudyWaveId = StudyWaveId;
            newSurveyPageCompleted.StudyId = StudyId;
            newSurveyPageCompleted.ConditionId = ConditionId;
            db.SurveyPageCompleteds.InsertOnSubmit(newSurveyPageCompleted);
            for (int i = 0; i < Names.Count(); i++)
            {
                SurveyResult sr = new SurveyResult();
                sr.ControlName = sw.VariableNamePrefix + Names[i];// +" " + "Survey Page: " + SurveyPageId.ToString() + " Study Wave Id: " + StudyWaveId.ToString() + " SurveyId: " + SurveyId.ToString();
                sr.ControlValue = Values[i];
                sr.SurveyPageCompleted = newSurveyPageCompleted;
                db.SurveyResults.InsertOnSubmit(sr);
            }
            db.SubmitChanges();
        }

        public bool IsIndividualJoined(int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            int Count = IndividualConditions.Count(x => x.IndividualId == IndividualId & x.StudyId == StudyId);
            if (Count == 0)
                return false;
            else
                return true;
        }
        public void OpenSession(int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            //Check for any open sessions
            var Results = from session in db.Sessions
                          where session.IndividualId == IndividualId
                          & session.StudyId == this.StudyId
                          & session.DateEnd == null
                          select session;
            foreach (Session s in Results)
            {
                s.DateEnd = s.DateBegin;
            }
            db.SubmitChanges();
            //Create a new open session
            Session newSession = new Session();
            newSession.IndividualId = IndividualId;
            newSession.StudyId = this.StudyId;
            newSession.DateBegin = DateTime.Now;
            db.Sessions.InsertOnSubmit(newSession);
            db.SubmitChanges();
        }
        public void CloseSession(int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            var Results = from session in db.Sessions
                          where session.IndividualId == IndividualId
                          & session.StudyId == this.StudyId
                          & session.DateEnd == null
                          select session;
            foreach (Session s in Results)
            {
                s.DateEnd = DateTime.Now;
            }
            db.SubmitChanges();
        }
        public void AddIndividualToStudy(int IndividualId, bool IsTesting)
        {
            dbDataContext db = new dbDataContext();
            IndividualCondition ic = new IndividualCondition();
            ic.IndividualId = IndividualId;
            ic.ConditionId = SelectNextCondition();
            ic.StudyId = this.StudyId;
            ic.IsTesting = IsTesting;
            ic.DateStart = DateTime.Now;
            db.IndividualConditions.InsertOnSubmit(ic);
            db.SubmitChanges();
        }
        public static void DeleteIndividualFromStudy(Guid StudyId, int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            List<IndividualCondition> ics = (from x in db.IndividualConditions
                                             where x.IndividualId == IndividualId
                                             && x.StudyId == StudyId
                                             select x).ToList(); 
            db.IndividualConditions.DeleteAllOnSubmit(ics);  
            List<StudyNotification> Notifications = (from y in db.StudyNotifications
                                                     where y.IndividualId == IndividualId
                                                     & y.StudyId == StudyId
                                                     select y).ToList();
            db.StudyNotifications.DeleteAllOnSubmit(Notifications);
            List<SurveyPageCompleted> pages = (from y in db.SurveyPageCompleteds
                                               where y.IndividualId == IndividualId
                                               && y.StudyId == StudyId
                                               select y).ToList();
            foreach (SurveyPageCompleted page in pages)
            {               
                db.SurveyResults.DeleteAllOnSubmit(page.SurveyResults);
            }
            db.SurveyPageCompleteds.DeleteAllOnSubmit(pages);
            db.SubmitChanges();
        }
        public int SelectNextCondition()
        {
            List<ConditionReport> list = GetConditionReport();
            return list[0].ConditionId;
        }
        public List<ConditionReport> GetConditionReport()
        {
            dbDataContext db = new dbDataContext();
            var Results = from condition in db.Conditions
                          where condition.StudyId == this.StudyId
                          orderby condition.IndividualConditions.Count
                          select new ConditionReport
                          {
                              ConditionId = condition.ConditionId
                            ,
                              TotalParticipants = condition.IndividualConditions.Count
                          };
            return Results.ToList();
        }
        public bool IsOpen
        {
            get
            {
                // Check to see if there's an OpenDate. If not assume no.
                if (this.OpenDate.HasValue)
                {
                    // See if the time is greater than now ...
                    if (System.DateTime.Now > this.OpenDate)
                    {
                        // See if there is a participant limit
                        if (this.MaxParticipants.HasValue)
                        {
                            int MaxParticipants = this.MaxParticipants.Value;
                            int Count = Individual.CountParticipants(this.StudyId);
                            if (Count < MaxParticipants)
                                return true;
                            else
                                return false;
                        }
                        else
                        {
                            // No limit, so it's open
                            return true;
                        }
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
                return true;
            }
        }
        public string CreateInitializationParameters(int IndividualId, bool Testing)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("StudyId=" + this.StudyId.ToString());
            sb.Append(",UserId=" + IndividualId.ToString());
            sb.Append(",IsDevelopment=" + Testing.ToString());
            return sb.ToString();
        }
        public List<StudyConsolidatedOrdered> GetStudyPages(int IndividualId, int StudyWaveId)
        {
            dbDataContext db = new dbDataContext();
            IndividualCondition ic = Study.GetConditionForIndividual(this.StudyId, IndividualId);
            return (from x in db.StudyConsolidatedOrdereds
                    where x.StudyId == this.StudyId
                    && x.ConditionId == ic.ConditionId
                    && x.StudyWaveId == StudyWaveId
                    orderby x.StudyWaveSurveyDisplayOrder,
                            x.SurveyPageDisplayOrder
                    select x).ToList();
        }
        public List<StudyConsolidatedOrderedIndividualResult> GetIncompleteStudyPages(int IndividualId, int StudyWaveId)
        {
            dbDataContext db = new dbDataContext();
            IndividualCondition ic = Study.GetConditionForIndividual(this.StudyId, IndividualId);
            return (from x in db.StudyConsolidatedOrderedIndividualResults
                    where x.StudyId == this.StudyId
                    && x.ConditionId == ic.ConditionId
                    && x.StudyWaveId == StudyWaveId
                    && x.DateCompleted == null
                    && x.IndividualId == IndividualId
                    orderby x.StudyWaveSurveyDisplayOrder,
                            x.SurveyPageDisplayOrder
                    select x).ToList();
        }
        public List<StudyConsolidatedOrderedResults> GetStudyPagesWithResults(int IndividualId, int StudyWaveId)
        {
            dbDataContext db = new dbDataContext();
            IndividualCondition ic = Study.GetConditionForIndividual(this.StudyId, IndividualId);
            return (from x in db.StudyConsolidatedOrdereds
                    where x.StudyId == this.StudyId
                    && x.ConditionId == ic.ConditionId
                    && x.StudyWaveId == StudyWaveId
                    orderby x.StudyWaveDisplayOrder,
                    x.StudyWaveSurveyDisplayOrder,
                    x.SurveyPageDisplayOrder
                    select new StudyConsolidatedOrderedResults
                    {
                        ConditionId = x.ConditionId
                        ,
                        ConditionName = x.ConditionName
                        ,
                        DateClose = x.DateClose
                        ,
                        DateOpen = x.DateOpen
                        ,
                        DayStartOffset = x.DayStartOffset
                        ,
                        OpenDate = x.OpenDate
                        ,
                        PageName = x.PageName
                        ,
                        StudyWaveDisplayOrder = x.StudyWaveDisplayOrder
                        ,
                        StudyWaveId = x.StudyWaveId
                        ,
                        StudyWaveName = x.StudyWaveName
                        ,
                        SurveyDisplayOrder = x.StudyWaveSurveyDisplayOrder
                        ,
                        SurveyId = x.SurveyId
                        ,
                        PageContentOriginal = x.PageContent
                        ,
                        SurveyName = x.SurveyName
                        ,
                        SurveyPageDisplayOrder = x.SurveyPageDisplayOrder
                        ,
                        SurveyPageId = x.SurveyPageId
                        ,
                        StudyId = x.StudyId
                        ,
                        PageContentModified = (from y in db.SurveyPageCompleteds
                                               where y.IndividualId == IndividualId
                                              && y.StudyId == x.StudyId
                                              && y.StudyWaveId == x.StudyWaveId
                                              && y.SurveyId == x.SurveyId
                                              && y.ConditionId == x.ConditionId
                                              && y.SurveyPageId == x.SurveyPageId
                                               select y).SingleOrDefault().PageContent
                        ,
                        IndividualId = (from y in db.SurveyPageCompleteds
                                        where y.IndividualId == IndividualId
                                       && y.StudyId == x.StudyId
                                       && y.StudyWaveId == x.StudyWaveId
                                       && y.SurveyId == x.SurveyId
                                       && y.ConditionId == x.ConditionId
                                       && y.SurveyPageId == x.SurveyPageId
                                        select y).SingleOrDefault().IndividualId
                    }).ToList();




        }

        public static List<ConditionCount> GetConditionCount(Guid StudyId)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.Conditions
                    where x.StudyId == StudyId
                    select new ConditionCount
                    {
                        ConditionId = x.ConditionId
                        ,
                        ConditionName = x.ConditionName
                        ,
                        Participants = x.IndividualConditions.Count
                    }).ToList();

        }
        public static int GetParticipantCount(Guid StudyId)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.Studies
                    where x.StudyId == StudyId
                    select x.IndividualConditions.Count).SingleOrDefault();

        }
        public static List<StudyResultsIndividualEmail> GetStudyResults(Guid StudyId)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.StudyResultsIndividualEmails
                    where x.StudyId == StudyId
                    orderby x.DateStart descending
                    select x).ToList();
        }
    }
    public partial class Survey
    {
        public static List<Survey> GetSurvey(Guid[] StudyIds)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.Surveys
                    where x.StudyWaveSurveys.Any(y => StudyIds.Contains(y.StudyWave.StudyId))
                    select x).ToList();
        }
    }
    public partial class StudyWave
    {
        public static List<StudyWave> GetStudyWaves(Guid[] StudyIds)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.StudyWaves
                    where StudyIds.Contains(x.StudyId)
                    orderby x.DayStartOffset
                    select x).ToList();
        }
    }
    public partial class IndividualClaim
    {
        public static void CreateIndividualClaim(int IndividualId, ClaimEnum ClaimToCreate, string Value, string DataType)
        {
            dbDataContext db = new dbDataContext();
            bool Insert = false;
            IndividualClaim ic = IndividualClaim.GetIndividualClaim(ClaimToCreate, IndividualId);
            if (ic == null)
            {
                ic = new IndividualClaim();
                Insert = true;
            }
            ic.IndividualId = IndividualId;
            ic.Value = Value;
            ic.DataType = DataType;
            ic.ClaimId = (int)ClaimToCreate;
            if (Insert)
            {
                db.IndividualClaims.InsertOnSubmit(ic);
            }
            db.SubmitChanges();
        }
        public static IndividualClaim GetIndividualClaim(ClaimEnum ClaimToSelect, int IndividualId)
        {
            dbDataContext db = new dbDataContext();
            IndividualClaim ic = db.IndividualClaims.SingleOrDefault(x => x.ClaimId == (int)ClaimToSelect & x.IndividualId == IndividualId);
            return ic;
        }
        public static void DeleteIndividualClaim(int IndividualId, ClaimEnum ClaimToDelete)
        {
            dbDataContext db = new dbDataContext();
            IndividualClaim ic = db.IndividualClaims.SingleOrDefault(x => x.IndividualId == IndividualId & x.ClaimId == (int)ClaimToDelete);
            if (ic != null)
            {
                db.IndividualClaims.DeleteOnSubmit(ic);
                db.SubmitChanges();
            }
        }
    }
    public partial class Condition
    {
        public static List<Condition> GetConditions(Guid[] StudyIds)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.Conditions
                    where StudyIds.Contains(x.StudyId)
                    orderby x.ConditionName
                    select x).ToList();

        }
        public int Random
        {
            get
            {
                Random myRand = new Random();
                return myRand.Next(0, 100);
            }
        }
    }
    public partial class IndividualCondition
    {

    }
    public partial class StudyWaveSurvey
    {
        public static List<StudyWaveSurvey> GetStudyWaveSurvey(Guid[] StudyIds)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.StudyWaveSurveys
                    where StudyIds.Contains(x.StudyWave.StudyId)
                    select x).ToList();
        }
    }
    public partial class SurveySurveyPage
    {
        public static List<SurveySurveyPage> GetSurveySurveyPage(Guid[] SurveyIds)
        {
            dbDataContext db = new dbDataContext();
            return (from x in db.SurveySurveyPages
                    where SurveyIds.Contains(x.SurveyId)
                    select x).ToList();
        }
    }
    public partial class StudyNotificationList
    {
        public Guid StudyId { get; set; }
        public int ConditionId { get; set; }
        public int StudyWaveId { get; set; }
        public int IndividualId { get; set; }
        public string EmailAddress { get; set; }
    }
    public partial class StudyNotification
    {
        public static List<StudyNotificationList> List()
        {
            using (dbDataContext db = new dbDataContext())
            {
                List<StudyNotificationList> individuals =
                    (from y in db.GetIndividualsCurrentLocationLastNotifications
                     // The opening window of the wave
                     where y.DateStart.AddDays(y.DayStartOffset.Value) < DateTime.Now
                     // The wave is to notify users by email
                     & y.IsUserNotifiedByEmail == true
                         // The closing window of the wave
                      & DateTime.Now < y.DateStart.AddDays(y.DayEndOffset.Value)
                         // The person must not have been notified within 24 hours
                      & (y.NotificationDateTime.HasValue == false ||
                      y.NotificationDateTime.Value.AddDays(1) < DateTime.Now)
                         // And the person is not working on the survey right now
                         // in the last hour ... i.e, they just started on it.
                      & y.DateStart.AddMinutes(60) < DateTime.Now
                         // And we can check the most recent date of a completed
                         // page to make sure they aren't still working through
                         // a survey                                              
                      & (y.LastPageCompletedDate.HasValue == false ||
                      y.LastPageCompletedDate.Value.AddMinutes(60) < DateTime.Now)
                     select new StudyNotificationList
                     {
                         ConditionId = y.ConditionId
                         ,
                         EmailAddress = y.EmailAddress
                         ,
                         IndividualId = y.IndividualId
                         ,
                         StudyId = y.StudyId
                         ,
                         StudyWaveId = y.StudyWaveId
                     }).ToList();
                return individuals;
            }
        }
        public static bool ReportNotification(int IndividualId
            , Guid StudyId, int StudyWaveId, DateTime NotificationDate)
        {
            using (dbDataContext db = new dbDataContext())
            {
                StudyNotification sn = new StudyNotification();
                sn.IndividualId = IndividualId;
                sn.StudyId = StudyId;
                sn.StudyWaveId = StudyWaveId;
                sn.NotificationDateTime = NotificationDate;
                db.StudyNotifications.InsertOnSubmit(sn);
                db.SubmitChanges();
                return true;
            }
        }
    }
}
